| HiveQL常用查询语句where、group by、having、join子句记录 | 您所在的位置：网站首页 › hive like语句 › HiveQL常用查询语句where、group by、having、join子句记录 |
|
由于hivesql中查询语句时，常用查询条件比较多，所以专门写一篇博文对HiveQL的查询语句进行总结，理清联系和区别。 目录 1. where子句（不能跟别名） （1）比较运算符 （2）like和rlike（正则匹配） （3）逻辑运算符and or not 2. group by子句（每...） 3. having子句（只用于group by之后） 4. join子句（A join B on A.key = B.key where X.key is null） （1）A join B on A.字段 = B.字段，连接两张表 （2）内连接（求交集） （3）左外连接（求左表独有） （4）右外连接（求右表独有） （5）全外连接（求并集） （6）去重全外连接（求并集并且去除交集部分） （7）多表连接 （8）笛卡尔积 1. where子句where子句使用一般紧接from tablename，并且where子句中不能使用字段别名。 select ~ from tablename where语句 （1）比较运算符< > 等价于= !（需要注意的是，会排除掉null值，如果想保留null，则用where a.id10 or a is null） is null is not null； A (not) between B and C：A介于[B,C]之间； A in (B ，C)：使用in运算列表中的值，即查询A=B、A=C的情况； （2）like和rlike（正则匹配）%代表任意个字符，通配符；_代表一个字符 A (not) like 'x%'：A必须以x开头 A (not) like '%x'：A必须以x结尾 A (not) like '%x%'：A必须包含x A (not) like ‘_5%’：A第2位数是5 A rlike '[Java正则表达式]' ps：必须加单引号 （3）逻辑运算符and or not同时查询两个字段，逻辑并、逻辑或、逻辑否 2. group by子句（每...）group by子句通常和聚合函数一起使用，后接分组字段，分组后对每个组进行聚合操作 在不使用聚合函数的时候，group by 子句中必须包含所有的列，即select A，B，C from table group by A，B，C；否则会报错，但这样group by就只有去重的意义了；所以一般group by都需要跟聚合函数配合使用； 一般用于需求中要求每个XXX，便对该XXX字段进行group by分组。 此外，group by后和where后都不能接字段别名。 计算emp表中每个部门每个岗位的最高薪水 select deptno,job,max(sal) max_sal from emp group by deptno,job; +---------+------------+----------+--+ | deptno | job | max_sal | +---------+------------+----------+--+ | 10 | CLERK | 1300.0 | | 10 | MANAGER | 2450.0 | | 10 | PRESIDENT | 5000.0 | | 20 | ANALYST | 3000.0 | | 20 | CLERK | 1100.0 | | 20 | MANAGER | 2975.0 | | 30 | CLERK | 950.0 | | 30 | MANAGER | 2850.0 | | 30 | SALESMAN | 1600.0 | +---------+------------+----------+--+ps：groupby，对多行聚合后输出一行的结果；若没有max（col）、collect_set(col)、concat_ws('~'，col)等行转列，即多行变一行的sql条件，是没法聚合的会报错。 3. having子句（只用于group by之后）having只用于group by子句后，给分组字段添加条件； 一般是跟聚合函数，例如select count(*) from table group by a having count(*)>10这种，having可以接别名 求每个部门的平均薪水大于2000的部门 select deptno,avg(sal) avg_sal from emp group by deptno having avg_sal>2000; +---------+---------------------+--+ | deptno | avg_sal | +---------+---------------------+--+ | 10 | 2916.6666666666665 | | 20 | 2175.0 | +---------+---------------------+--+ 4. join子句（A join B on A.key = B.key where X.key is null） （1）A join B on A.字段 = B.字段，连接两张表例如两张hive表，员工表emp和部门表dept，通过deptno来串联 要求：根据员工表和部门表中的部门编号，查询员工编号、员工名称和部门名称 0: jdbc:hive2://hadoop100:10000> select e.empno,e.ename,d.dname from emp e join dept d on e.deptno = d.deptno; +----------+----------+-------------+--+ | e.empno | e.ename | d.dname | +----------+----------+-------------+--+ | 7369 | SMITH | RESEARCH | | 7499 | ALLEN | SALES | | 7521 | WARD | SALES | | 7566 | JONES | RESEARCH | | 7654 | MARTIN | SALES | | 7698 | BLAKE | SALES | | 7782 | CLARK | ACCOUNTING | | 7788 | SCOTT | RESEARCH | | 7839 | KING | ACCOUNTING | | 7844 | TURNER | SALES | | 7876 | ADAMS | RESEARCH | | 7900 | JAMES | SALES | | 7902 | FORD | RESEARCH | | 7934 | MILLER | ACCOUNTING | +----------+----------+-------------+--+ （2）内连接（求交集） select * from A a join B b on a.key = b.key; （3）左外连接（求左表独有） select * from A a left join B b on a.key = b.key where b.key is null; （4）右外连接（求右表独有） select * from A a left join B b on a.key = b.key where a.key is null; （5）全外连接（求并集） select * from A a full join B b on a.key = b.key; （6）去重全外连接（求并集并且去除交集部分） select * from A a full join B b on a.key = b.key where a.key is null or b.key is null; （7）多表连接 select a.name, b.name, c.name from A a join B b on a.key1 = b.key1 join C c on a.key2 = c.key2;ps：hive从左到右顺序执行，多表连接join顺序也是按照查询字段从上到下。 （8）笛卡尔积 0: jdbc:hive2://hadoop100:10000> select empno,dname from emp, dept; |
| 今日新闻 |
| 推荐新闻 |
| 专题文章 |
| CopyRight 2018-2019 实验室设备网 版权所有 |